package troopmanager.mooresr.us;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.ListIterator;
import java.util.Set;

public class DataManager {
	private Connection conn = null;
	private static DataManager instance = null;
	
	public static DataManager getInstance() {
		if (instance == null) {
			instance = new DataManager();
		}
		
		return instance;
		
	}
	
	private DataManager() {
	
	}
	
	public int init(String user, String password) {
		int result = 0;
		
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			result = -1;
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection("jdbc:mysql://www.moores-r.us:5222/trooprecords" +
					"?user=" + user + "&password=" + password);
		} catch (SQLException ex) {
			System.out.println("Fail");
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    result = -1;
		}
		
		return result;
	}
	
	public List<Person> getAllPeople() {
		return getAllPeople(false);
	}
	
	public List<Person> getAllPeople(Boolean includeInactive) {
		List<Person> result = new LinkedList<Person>();
		Statement stmt;
		ResultSet resultSet;
				
		try {
			stmt = conn.createStatement();
			if (includeInactive) {
				resultSet = stmt.executeQuery("SELECT * FROM people_with_detail ORDER BY last_name, first_name");
			} else {
				resultSet = stmt.executeQuery("SELECT * FROM people_with_detail WHERE active = 1 ORDER BY last_name, first_name");
			}

			while (resultSet.next()) {
				Person p = createPersonFromResultSet(resultSet);
				result.add(p);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return result;
		};
		
		return result;		
	}
	
	public List<Person> getAllAdults() {
		List<Person> result = new LinkedList<Person>();
		Statement stmt;
		ResultSet resultSet;
				
		try {
			stmt = conn.createStatement();
			resultSet = stmt.executeQuery("SELECT * FROM people_with_detail WHERE type = 1 AND active = 1 ORDER BY last_name, first_name");

			while (resultSet.next()) {
				Person p = createPersonFromResultSet(resultSet);
				result.add(p);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return result;
		};
		
		return result;		
	}
	
	public List<Person> getAllScouts() {
		List<Person> result = new LinkedList<Person>();
		Statement stmt;
		ResultSet resultSet;
				
		try {
			stmt = conn.createStatement();
			resultSet = stmt.executeQuery("SELECT * FROM people_with_detail WHERE type = 2 AND active = 1 ORDER BY last_name, first_name");

			while (resultSet.next()) {
				Person p = createPersonFromResultSet(resultSet);
				result.add(p);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return result;
		};
		
		return result;		
	}
	
	public List<Vehicle> getAllVehicles() {
		List<Vehicle> result = new LinkedList<Vehicle>();
		Statement stmt;
		ResultSet resultSet;
				
		try {
			stmt = conn.createStatement();
			resultSet = stmt.executeQuery("SELECT * FROM vehicle_view");

			while (resultSet.next()) {
				Vehicle v = createVehicleFromResultSet(resultSet);
				result.add(v);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return result;
		};		
		return result;
	}

	
	public List<Event> getAllEvents(Boolean includePast) {
		List<Event> result = new LinkedList<Event>();
		Statement stmt;
		ResultSet resultSet;
				
		try {
			stmt = conn.createStatement();
			if (includePast) {
				resultSet = stmt.executeQuery("SELECT * FROM events ORDER BY departure_date");
			} else {
				resultSet = stmt.executeQuery("SELECT * FROM events WHERE departure_date >= now() ORDER BY departure_date");
			}

			while (resultSet.next()) {
				Event e = createEventFromResultSet(resultSet);
				result.add(e);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		};	
		
		try {
			String query = "SELECT form_name, form_id FROM event_special_form_names WHERE event_id = ?";
			PreparedStatement pstmt = conn.prepareStatement(query);
			for (Event e : result) {			
				Integer eventId = e.getEventId();
				pstmt.setInt(1, eventId);
				ResultSet resultSet2 = pstmt.executeQuery();
				while (resultSet2.next()) {
					String formName = resultSet2.getString("form_name");
					Integer formId = resultSet2.getInt("form_id");
					e.addSpecialFormName(new EventSpecialFormName(formId, formName));
				}
			}
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}
		
		
		return result;
	}
	
	private Event createEventFromResultSet(ResultSet resultSet) {
		Integer intVal;
		String strVal;
		Date dateVal;
		
		Event e = new Event();

		try {
			intVal = resultSet.getInt("event_id");
			e.setEventId(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			strVal = resultSet.getString("event_name");
			e.setEventName(strVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			strVal = resultSet.getString("departure_location");
			e.setDepartureLocation(strVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			strVal = resultSet.getString("destination");
			e.setDestination(strVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			dateVal = resultSet.getDate("departure_date");
			e.setDepartureDate(dateVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			dateVal = resultSet.getDate("return_date");
			e.setReturnDate(dateVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			intVal = resultSet.getInt("permit_filed");
			if (intVal == 0) {
				e.setPermitFiled(false);
			} else {
				e.setPermitFiled(true);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("swimming");
			if (intVal == 0) {
				e.setSwimming(false);
			} else {
				e.setSwimming(true);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			intVal = resultSet.getInt("boating");
			if (intVal == 0) {
				e.setBoating(false);
			} else {
				e.setBoating(true);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			intVal = resultSet.getInt("climbing");
			if (intVal == 0) {
				e.setClimbing(false);
			} else {
				e.setClimbing(true);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			intVal = resultSet.getInt("flying");
			if (intVal == 0) {
				e.setFlying(false);
			} else {
				e.setFlying(true);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			intVal = resultSet.getInt("high_adventure");
			if (intVal == 0) {
				e.setHighAdventure(false);
			} else {
				e.setHighAdventure(true);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("travel_miles");
			e.setTravelMiles(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("tour_leader");
			e.setLeaderId(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("asst_tour_leader");
			e.setAsstLeaderId(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		return e;

	}
	
	
	public EventParticipant createEventParticipantFromResultSet(ResultSet resultSet) {
		Integer intVal;
		
		EventParticipant ep = new EventParticipant();

		try {
			intVal = resultSet.getInt("person_id");
			ep.setPerson(DataManager.getInstance().getPerson(intVal));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("event_id");
			ep.setEventId(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("paid");
			if (intVal == 0) {
				ep.setPaid(false);
			} else {
				ep.setPaid(true);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("permission_slip");
			if (intVal == 0) {
				ep.setPermissionSlip(false);
			} else {
				ep.setPermissionSlip(true);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		return ep;
	}
	
	public List<EventParticipant> getEventParticipants(Event e) {
		List<EventParticipant> result = new LinkedList<EventParticipant>();
		Statement stmt;
		ResultSet resultSet;
		
		try {
			stmt = conn.createStatement();
			resultSet = stmt.executeQuery("SELECT event_participants.* " +
					"FROM people, event_participants " +
					"WHERE event_participants.person_id = people.person_id " +
					"AND event_participants.event_id = " + e.getEventId() + 
					" ORDER BY people.last_name, people.first_name");
			
			while (resultSet.next()) {
				result.add(createEventParticipantFromResultSet(resultSet));
				
			}
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
			return result;
		};		
		return result;		
	}

	public void updatePerson(Person p) {
		Statement stmt;
		ResultSet resultSet;
		
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM people WHERE person_id = " + p.getPersonId());
			resultSet.next();
			
			resultSet.updateString("first_name", p.getFirstName());
			resultSet.updateString("last_name", p.getLastName());
			resultSet.updateInt("type", p.getType());
			resultSet.updateDate("med_form_a", p.getMedFormA());
			resultSet.updateDate("med_form_b", p.getMedFormB());
			resultSet.updateDate("med_form_c", p.getMedFormC());
			resultSet.updateString("drivers_license", p.getDriversLicense());
			resultSet.updateBoolean("active", p.getActive());
			resultSet.updateString("gender", p.getGender());
			
			resultSet.updateRow();
			
			resultSet = stmt.executeQuery("SELECT * FROM people_detail WHERE person_id = " + p.getPersonId());
			resultSet.next();
			resultSet.updateDate("dob", p.getDob());
			resultSet.updateString("cell_phone", p.getCellPhone());
			resultSet.updateString("home_phone", p.getHomePhone());
			resultSet.updateString("work_phone", p.getWorkPhone());
			resultSet.updateString("email", p.getEmail());
			
			resultSet.updateRow();
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
	}
	
	public void addPerson(Person p) {
		Statement stmt;
		ResultSet resultSet;
		Integer newPersonId;
		
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM people");
			resultSet.moveToInsertRow();
			
			resultSet.updateString("first_name", p.getFirstName());
			resultSet.updateString("last_name", p.getLastName());
			resultSet.updateInt("type", p.getType());
			resultSet.updateDate("med_form_a", p.getMedFormA());
			resultSet.updateDate("med_form_b", p.getMedFormB());
			resultSet.updateDate("med_form_c", p.getMedFormC());
			resultSet.updateString("drivers_license", p.getDriversLicense());
			resultSet.updateBoolean("active", p.getActive());
			resultSet.updateString("gender", p.getGender());
			
			resultSet.insertRow();
			
			resultSet.last();
			newPersonId = resultSet.getInt("person_id");
			
			resultSet = stmt.executeQuery("SELECT * FROM people_detail");
			resultSet.moveToInsertRow();
			
			resultSet.updateInt("person_id", newPersonId);
			resultSet.updateDate("dob", p.getDob());
			resultSet.updateString("cell_phone", p.getCellPhone());
			resultSet.updateString("home_phone", p.getHomePhone());
			resultSet.updateString("work_phone", p.getWorkPhone());
			resultSet.updateString("email", p.getEmail());
			
			resultSet.insertRow();
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
	public void deletePerson(Person p) {
		Statement stmt;
		ResultSet resultSet;
		
		// TODO:  This might need to cascade the delete, unless we're going to rely on mysql to do it for us
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM people_with_detail WHERE person_id = " + p.getPersonId());	
			
			resultSet.next();
			resultSet.deleteRow();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

	public Person getPerson(Integer personId) {
		Statement stmt;
		ResultSet resultSet;
		Person result = null;
		
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			resultSet = stmt.executeQuery("SELECT * FROM people_with_detail WHERE person_id = " + personId);
			
			if (resultSet.next()) {
				result=createPersonFromResultSet(resultSet);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
		return result;
	}
	
	private Person createPersonFromResultSet(ResultSet resultSet) {
		Integer intVal;
		String strVal;
		Date dateVal;
		
		Person p = new Person();

		try {
			intVal = resultSet.getInt("person_id");
			p.setPersonId(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			strVal = resultSet.getString("first_name");
			p.setFirstName(strVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			strVal = resultSet.getString("last_name");
			p.setLastName(strVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			intVal = resultSet.getInt("type");
			p.setType(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			dateVal = resultSet.getDate("med_form_a");
			p.setMedFormA(dateVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			dateVal = resultSet.getDate("med_form_b");
			p.setMedFormB(dateVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			dateVal = resultSet.getDate("med_form_c");
			p.setMedFormC(dateVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			strVal = resultSet.getString("drivers_license");
			p.setDriversLicense(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("active");
			if (intVal == 0) {
				p.setActive(false);
			} else {
				p.setActive(true);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			dateVal = resultSet.getDate("dob");
			p.setDob(dateVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			strVal = resultSet.getString("cell_phone");
			p.setCellPhone(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			strVal = resultSet.getString("home_phone");
			p.setHomePhone(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			strVal = resultSet.getString("work_phone");
			p.setWorkPhone(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			strVal = resultSet.getString("email");
			p.setEmail(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			strVal = resultSet.getString("gender");
			p.setGender(strVal);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return p;
	
	}
	
	
	public void updateVehicle(Vehicle v) {
		Statement stmt;
		ResultSet resultSet;
		
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM vehicles WHERE vehicle_id = " + v.getVehicleId());
			resultSet.next();
			
			resultSet.updateString("make", v.getMake());
			resultSet.updateString("model", v.getModel());
			resultSet.updateInt("model_year", v.getYear());
			resultSet.updateInt("seatbelts", v.getSeatbelts());
			resultSet.updateInt("liability_per_person", v.getLiabilityPerPerson());
			resultSet.updateInt("liability_per_accident", v.getLiabilityPerAccident());
			resultSet.updateInt("property_damage", v.getPropertyDamage());
			resultSet.updateString("license_plate", v.getLicensePlate());
			resultSet.updateInt("owner_id", v.getOwnerId());
			
			resultSet.updateRow();
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
	}
	
	public void addVehicle(Vehicle v) {
		Statement stmt;
		ResultSet resultSet;
		
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM vehicles");
			resultSet.moveToInsertRow();
			resultSet.updateString("make", v.getMake());
			resultSet.updateString("model", v.getModel());
			resultSet.updateInt("model_year", v.getYear());
			resultSet.updateInt("seatbelts", v.getSeatbelts());
			resultSet.updateInt("liability_per_person", v.getLiabilityPerPerson());
			resultSet.updateInt("liability_per_accident", v.getLiabilityPerAccident());
			resultSet.updateInt("property_damage", v.getPropertyDamage());
			resultSet.updateString("license_plate", v.getLicensePlate());
			resultSet.updateInt("owner_id", v.getOwnerId());
			resultSet.insertRow();
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
	}
	
	public void deleteVehicle(Vehicle v) {
		Statement stmt;
		ResultSet resultSet;
		
		// TODO:  This might need to cascade the delete, unless we're going to rely on mysql to do it for us
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM vehicles WHERE vehicle_id = " + v.getVehicleId());	
			
			resultSet.next();
			resultSet.deleteRow();

			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
	public Vehicle getVehicle(Integer vehicleId) {
		Statement stmt;
		ResultSet resultSet;
		Vehicle result = null;
		
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			resultSet = stmt.executeQuery("SELECT * FROM vehicle_view WHERE vehicle_id = " + vehicleId);
			
			if (resultSet.next()) {
				result=createVehicleFromResultSet(resultSet);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		
		return result;
	}
	
	public void updateEvent(Event e) {
		Statement stmt;
		ResultSet resultSet;
		
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM events WHERE event_id = " + e.getEventId());
			resultSet.next();
			
			resultSet.updateString("event_name", e.getEventName());
			resultSet.updateString("departure_location", e.getDepartureLocation());
			resultSet.updateString("destination", e.getDestination());
			resultSet.updateDate("departure_date", e.getDepartureDate());
			resultSet.updateDate("return_date", e.getReturnDate());
			resultSet.updateBoolean("permit_filed", e.getPermitFiled());
			resultSet.updateBoolean("swimming", e.getSwimming());
			resultSet.updateBoolean("boating", e.getBoating());
			resultSet.updateBoolean("climbing", e.getClimbing());
			resultSet.updateBoolean("flying", e.getFlying());
			resultSet.updateBoolean("high_adventure", e.getHighAdventure());
			resultSet.updateInt("travel_miles", e.getTravelMiles());
			resultSet.updateInt("tour_leader", e.getLeaderId());
			resultSet.updateInt("asst_tour_leader", e.getAsstLeaderId());

			
			resultSet.updateRow();
			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}
		
	}
	
	public void addEvent(Event e) {
		Statement stmt;
		ResultSet resultSet;
		
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM events");
			resultSet.moveToInsertRow();
			
			resultSet.updateString("event_name", e.getEventName());
			resultSet.updateString("departure_location", e.getDepartureLocation());
			resultSet.updateString("destination", e.getDestination());
			resultSet.updateDate("departure_date", e.getDepartureDate());
			resultSet.updateDate("return_date", e.getReturnDate());
			resultSet.updateBoolean("permit_filed", e.getPermitFiled());
			resultSet.updateBoolean("swimming", e.getSwimming());
			resultSet.updateBoolean("boating", e.getBoating());
			resultSet.updateBoolean("climbing", e.getClimbing());
			resultSet.updateBoolean("flying", e.getFlying());
			resultSet.updateBoolean("high_adventure", e.getHighAdventure());
			resultSet.updateInt("travel_miles", e.getTravelMiles());
			resultSet.updateInt("tour_leader", e.getLeaderId());
			resultSet.updateInt("asst_tour_leader", e.getAsstLeaderId());

			
			resultSet.insertRow();
			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}
		
	}
	
	public void deleteEvent(Event e) {
		Statement stmt;
		ResultSet resultSet;
		
		// TODO:  This might need to cascade the delete, unless we're going to rely on mysql to do it for us
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM events WHERE event_id = " + e.getEventId());	
			
			resultSet.next();
			resultSet.deleteRow();

			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}
	}

	public void setEventParticipants(Integer eventId, LinkedList<EventParticipant> participants) {
		Statement stmt;
		ResultSet resultSet;
		
		try {
			// Delete all event_participant records for this event
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM event_participants WHERE event_id = " + eventId);
			
			while (resultSet.next()) {
				resultSet.deleteRow();
			}
			
			// Add back all the new ones
			PreparedStatement pstmt = conn.prepareStatement("INSERT INTO event_participants " + 
					"(event_id, person_id, paid, permission_slip) VALUES (?, ?, ?, ?)");
			for (EventParticipant p : participants) {
				pstmt.setInt(1, p.getEventId());
				pstmt.setInt(2, p.getPerson().getPersonId());
				pstmt.setBoolean(3, p.getPaid());
				pstmt.setBoolean(4, p.getPermissionSlip());
				pstmt.executeUpdate();
			}
			
			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}
		
	}
	
	public List<Person> getEligibleDrivers(Event e) {
		LinkedList<Person> result = new LinkedList<Person>();
		Statement stmt;
		ResultSet resultSet;
		
		try {	

			 result = (LinkedList<Person>) getAllAdults();
			 
			 // Now get all the drivers already on the event
			 LinkedList<Integer> driverIds = new LinkedList<Integer>();
			 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			 resultSet = stmt.executeQuery("SELECT person_id " +
					 "FROM event_vehicle_drivers " +
					 "WHERE event_id = " + e.getEventId());
			 while (resultSet.next()) {
				 driverIds.add(resultSet.getInt("person_id"));
			 }
			 
			 // Now remove from allAdultParticipants any who are already drivers
			 for (Integer driverId : driverIds) {
				 for (ListIterator<Person> l = result.listIterator(); l.hasNext(); ) {
					if (l.next().getPersonId() == driverId) {
						l.remove();
					}
				 }
			 }
			 
			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}
		
		return result;
	}
	
	public List<Vehicle> getAvailableVehicles(Event e) {
		LinkedList<Vehicle> result = new LinkedList<Vehicle>();
		Statement stmt;
		ResultSet resultSet;
		
		try {	
			// First get all the vehicles
			result = (LinkedList<Vehicle>) getAllVehicles();
			 
			 // Now get all the vehicles already on the event
			 LinkedList<Integer> drivenVehicleIds = new LinkedList<Integer>();
			 stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			 resultSet = stmt.executeQuery("SELECT vehicle_id " +
					 "FROM event_vehicle_drivers " +
					 "WHERE event_id = " + e.getEventId());
			 while (resultSet.next()) {
				 drivenVehicleIds.add(resultSet.getInt("vehicle_id"));
			 }
			 
			 // Now remove from result any vehicles already being driven
			 for (Integer vehicleId : drivenVehicleIds) {
				 for (ListIterator<Vehicle> l = result.listIterator(); l.hasNext(); ) {
					if (l.next().getVehicleId() == vehicleId) {
						l.remove();
					}
				 }
			 }
			 
			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}
		
		return result;
	}

	public LinkedList<VehicleDriver> getAllVehicleDrivers(Event e) {
		LinkedList<VehicleDriver> result = new LinkedList<VehicleDriver>();
		Statement stmt;
		ResultSet resultSet;
		
		try {	
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			resultSet = stmt.executeQuery("SELECT vehicles.vehicle_id, vehicles.make, vehicles.model, " +
					"vehicles.model_year, vehicles.seatbelts, people.person_id, people.first_name, " + 
					"people.last_name, owners.first_name as owner_first_name, " +
					"owners.last_name as owner_last_name " +
					"FROM vehicles, people, event_vehicle_drivers, people as owners " +
					"WHERE event_vehicle_drivers.vehicle_id = vehicles.vehicle_id " +
					"AND event_vehicle_drivers.person_id = people.person_id " +
					"AND event_vehicle_drivers.event_id = " + e.getEventId() + " " +
					"AND vehicles.owner_id = owners.person_id");
			
			while (resultSet.next()) {
				result.add(createVehicleDriverFromResultSet(resultSet));
			}
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}
		
		return result;
		
	}
	
	private Vehicle createVehicleFromResultSet(ResultSet resultSet) {
		Integer intVal;
		String strVal;

		Vehicle v = new Vehicle();
		
		try {
			intVal = resultSet.getInt("vehicle_id");
			v.setVehicleId(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			strVal = resultSet.getString("make");
			v.setMake(strVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			strVal = resultSet.getString("model");
			v.setModel(strVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			intVal = resultSet.getInt("model_year");
			v.setYear(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("seatbelts");
			v.setSeatbelts(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			intVal = resultSet.getInt("liability_per_accident");
			v.setLiabilityPerAccident(intVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("liability_per_person");
			v.setLiabilityPerPerson(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("property_damage");
			v.setPropertyDamage(intVal);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		/*
		try {
			strVal = resultSet.getString("insurance_carrier");
			setInsuranceCarrier(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			strVal = resultSet.getString("policy_number");
			setPolicyNumber(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		*/
	
		try {
			strVal = resultSet.getString("license_plate");
			v.setLicensePlate(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			intVal = resultSet.getInt("person_id");
			v.setOwnerId(intVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			strVal = resultSet.getString("first_name");
			v.setOwnerFirstName(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			strVal = resultSet.getString("last_name");
			v.setOwnerLastName(strVal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return v;
	}
	
	public VehicleDriver createVehicleDriverFromResultSet(ResultSet resultSet) {
		
		VehicleDriver vd = new VehicleDriver();

		
		try {
			vd.setPersonId(resultSet.getInt("person_id"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			vd.setVehicleId(resultSet.getInt("vehicle_id"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			vd.setFirstName(resultSet.getString("first_name"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			vd.setLastName(resultSet.getString("last_name"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			vd.setMake(resultSet.getString("make"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			vd.setModel(resultSet.getString("model"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			vd.setYear(resultSet.getInt("model_year"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			vd.setOwnerFirstName(resultSet.getString("owner_first_name"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			vd.setOwnerLastName(resultSet.getString("owner_last_name"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		try {
			vd.setSeatbelts(resultSet.getInt("seatbelts"));
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		return vd;
	}


	public void setEventDrivers(Integer eventId, LinkedList<VehicleDriver> drivers) {
		Statement stmt;
		ResultSet resultSet;
		
		try {
			// Delete all event_vehicle_driver records for this event
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			resultSet = stmt.executeQuery("SELECT * FROM event_vehicle_drivers WHERE event_id = " + eventId);
			
			while (resultSet.next()) {
				resultSet.deleteRow();
			}
			
			// Add back all the new ones
			PreparedStatement pstmt = conn.prepareStatement("INSERT INTO event_vehicle_drivers " + 
					"(event_id, vehicle_id, person_id) VALUES (?, ?, ?)");
			for (VehicleDriver d : drivers) {
				pstmt.setInt(1, eventId);
				pstmt.setInt(2, d.getVehicleId());
				pstmt.setInt(3, d.getPersonId());
				pstmt.executeUpdate();
			}
			
			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}		
		
	}
	
	public HashMap<Integer, String> getAllTrainingTypes() {
		HashMap<Integer, String> result = new HashMap<Integer, String>();
		Statement stmt;
		ResultSet resultSet;
		
		try {
			stmt  = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			resultSet = stmt.executeQuery("SELECT * FROM training_types");
			
			while (resultSet.next()) {
				Integer key = resultSet.getInt("training_id");
				String value = resultSet.getString("description");
				result.put(key, value);
			}
			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}	
		
		return result;
	}

	public HashMap<Integer, Date> getTrainingRecord(Person p) {
		HashMap<Integer, Date> result = new HashMap<Integer, Date>();
		Statement stmt;
		ResultSet resultSet;
		
		try {
			stmt  = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			resultSet = stmt.executeQuery("SELECT * FROM training_record WHERE person_id = " + p.getPersonId());
			
			while (resultSet.next()) {
				Integer key = resultSet.getInt("training_id");
				Date value = resultSet.getDate("date_complete");
				result.put(key, value);
			}
			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}	
		
		return result;		
	}

	public void setTrainingRecord(Integer personId, HashMap<Integer, Date> trainingRecord) {
		Statement stmt;

		
		// TODO:  This might need to cascade the delete, unless we're going to rely on mysql to do it for us
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			stmt.executeUpdate("DELETE FROM training_record WHERE person_id = " + personId);	
			
			// Add back all the new ones
			PreparedStatement pstmt = conn.prepareStatement("INSERT INTO training_record " + 
					"(person_id, training_id, date_complete) VALUES (?, ?, ?)");
			for (Integer  id : trainingRecord.keySet()) {
				pstmt.setInt(1, personId);
				pstmt.setInt(2, id);
				pstmt.setDate(3, trainingRecord.get(id));
				pstmt.executeUpdate();
			}
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}	
		
	}
	
	public Set<EventSpecialForm> getSpecialForms(Integer eventId, Integer personId) {
		Statement stmt;
		ResultSet resultSet;
		HashSet<EventSpecialForm> result = new HashSet<EventSpecialForm>();
		
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			resultSet = stmt.executeQuery("SELECT form_id, person_id, received FROM " +
					"event_special_form_names LEFT OUTER JOIN event_special_forms " +
					"USING (form_id) WHERE event_id = " + eventId.toString() + " " +
					"AND person_id = " + personId.toString());
			
			while (resultSet.next()) {
				Integer person_id = resultSet.getInt("person_id");
				Integer form_id = resultSet.getInt("form_id");
				Boolean received = resultSet.getBoolean("received");
				EventSpecialForm esf = new EventSpecialForm(form_id, person_id, received);
				result.add(esf);
			}
			
		} catch (SQLException e1) {
			System.out.println(e1.getMessage());
		}	
		
		return result;
	
	}
	
	public void setSpecialForms(Set<EventSpecialForm> forms) {
		try {
			PreparedStatement pstmt = conn.prepareStatement("INSERT INTO event_special_forms " + 
					"(form_id, person_id, received) VALUES (?, ?, ?) " +
			"ON DUPLICATE KEY UPDATE received=?");

			for (EventSpecialForm  form : forms) {
				pstmt.setInt(1, form.getForm_id());
				pstmt.setInt(2, form.getPerson_id());
				pstmt.setBoolean(3, form.getReceived());
				pstmt.setBoolean(4, form.getReceived());
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}
	
}
